﻿using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DzInfo.Report.ND
{
    [Description("ND：库存总量考核指标"), HotUpdate]
    public class InvQtyJe : SysReportBaseService
    {
        public override void Initialize()
        {
            base.Initialize();
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.ReportName = new LocaleValue("库存总量考核指标", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = false;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            this.ReportProperty.PrimaryKeyFieldName = "FCK";
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
        }
        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string sortStr = filter.FilterParameter.SortString;
            if (sortStr.Length > 0)
            {
                return sortStr;
            }
            return " FCK ";
        }
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            var cusFilter = filter.FilterParameter.CustomFilter;
            //string seqFld = string.Format(base.KSQL_SEQ, BuilderTempTableOrderBySQL(filter));
            //string createSql = GetCreateSql();
            string dataSql = GetDataSql(cusFilter);
            string endSql = GetSql(tableName);
            string sql = dataSql + endSql;
            DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        private string GetSql(string tableName)
        {
            string endSql = $" select *  into {tableName} from #TEMP  ";
            return endSql;
        }

        private string GetDataSql(DynamicObject cusFilter)
        {
            var org = cusFilter["FKCZZ"] as DynamicObject;
            var stock = cusFilter["FCK"] as DynamicObject;
            var stockGroup = cusFilter["FCKFZ"] as DynamicObject;
            string filStr = "";
            if (org != null)
            {
                var orgId = Convert.ToInt32(org["id"]);
                filStr += $" and T0.FSTOCKORGID={orgId} ";
            }
            if (stock != null)
            {
                var stockid = Convert.ToInt32(stock["id"]);
                filStr += $" and T0.FSTOCKID={stockid} ";
            }
            if (stockGroup != null)
            {
                var fz = Convert.ToInt32(stockGroup["id"]);
                filStr += $" and T2.FGROUP={fz} ";
            }
            string dataSql = string.Format(@" /*dialect*/ 
            SELECT T4.FNAME FKCZZ,
                   T3.FNAME FCK,
	               T5.FNAME FCKFZ,
                   ROUND(ISNULL(SUM(T0.FBASEQTY * T1.DJ), 0), 2) AS FJMJE,
	               ISNULL(T1.FYEAR,0) FND,
	               ISNULL(T1.FPERIOD,0) FJJ,
	               ROUND(T2.F_POSW_FZ, 2) AS FFZ,
	               ROUND(ISNULL(SUM(T0.FBASEQTY * T1.DJ), 0) - T2.F_POSW_FZ, 2) AS FCY,
	               CASE WHEN ISNULL(SUM(T0.FBASEQTY*T1.DJ),0)-T2.F_POSW_FZ<=0 THEN '是' ELSE '否' END AS FYN,
                   ROW_NUMBER() OVER(ORDER BY  T3.FNAME  ) FIDENTITYID 
            INTO #TEMP
            FROM
              (SELECT T0.FSTOCKORGID,
                      T0.FSTOCKID,
                      T1.FNUMBER,
                      T0.FBASEQTY
               FROM T_STK_INVENTORY T0
               JOIN T_BD_MATERIAL T1 ON T1.FMATERIALID=T0.FMATERIALID
               WHERE T0.FBASEQTY<>0) T0
            LEFT JOIN
              (SELECT T0.FACCTGORGID ZZ,
                      T2.FNUMBER WLBM,
                      CASE
                          WHEN T1.FQTY=0 THEN 0
                          ELSE T1.FAMOUNT/T1.FQty
                      END DJ,
                      YEAR(T0.FDATE+1) FYEAR,
                      MONTH(T0.FDATE+1) FPERIOD
               FROM V_HS_OUTACCTG T0
               JOIN
                 (SELECT FACCTGORGID,
                         MAX(FYEAR) AS FYEAR,
                         MAX(CASE
                                 WHEN FYEAR = g.max_year THEN FPERIOD
                             END) AS FPERIOD
                  FROM V_HS_OUTACCTG
                  CROSS JOIN
                    (SELECT MAX(FYEAR) AS max_year
                     FROM V_HS_OUTACCTG) g
                  GROUP BY FACCTGORGID) T00 ON T00.FPERIOD=T0.FPERIOD
               AND T00.FYEAR=T0.FYEAR
               AND T00.facctgorgid=T0.facctgorgid
               JOIN V_HS_Balance T1 ON T1.FID=T0.FID
               JOIN T_BD_MATERIAL T2 ON T2.FMATERIALID=T1.FMASTERID) T1 ON T1.WLBM=T0.FNUMBER
            AND T1.ZZ=T0.FSTOCKORGID
            JOIN T_BD_STOCK T2 ON T2.FSTOCKID=T0.FSTOCKID
            JOIN T_BD_STOCK_L T3 ON T3.FSTOCKID=T2.FSTOCKID AND T3.FLOCALEID=2052
            JOIN T_ORG_ORGANIZATIONS_L T4 ON T4.FORGID=T0.FSTOCKORGID AND T4.FLOCALEID=2052
            LEFT JOIN T_BD_STOCKGROUP_L T5 ON T5.FID=T2.FGROUP AND T5.FLOCALEID=2052
            WHERE T1.FYEAR IS NOT NULL {0}
            GROUP BY T4.FNAME,T3.FNAME,T1.FYEAR,T1.FPERIOD,T2.F_POSW_FZ,T5.FNAME
            ;", filStr);
            return dataSql;
        }

        //private string GetCreateSql()
        //{
        //    string createSql = @"/*dialect*/ CREATE TABLE #TEMP (
        //    FKCZZ VARCHAR(255) NULL,
        //    FCK VARCHAR(255) NULL,
        //    FCKFZ VARCHAR(255) NULL,
        //    FJMJE DECIMAL(38,10)  NULL,
        //    FND INT NULL,
        //    FJJ INT NULL,
        //    FFZ DECIMAL(38,10)  NULL,
        //    FCY DECIMAL(38,10)  NULL,
        //    FYN VARCHAR(6)  NULL,
        //    FIDENTITYID INT NULL
        //    ); ";
        //    return createSql;
        //}
    }
}
